﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using Model;
using System.Text;
namespace Manager.Helper
{
    public class ServerExcel
    {
        public string SchoolCode { get; set; }
        public string SchoolName { get; set; }
        public string FilePath { get; set; }
        public UploadResultLogManager logManager = new UploadResultLogManager();
        public ServerExcel(string SchoolCode,string SchoolName,string FilePath)
        {
            this.FilePath = FilePath;
            this.SchoolCode = SchoolCode;
            this.SchoolName = SchoolName;
        }
        public DataSet ImportExcel()
        {
            UploadResultLog log = new UploadResultLog()
            {
                LogError = null,
                LogSchoolCode = SchoolCode,
                LogSchoolName = SchoolName,
                LogTime = DateTime.Now,
                LogType = "导入Excel",
                LogFileUpLoadTime = DateTime.Now,
            };
            if (System.IO.File.Exists(FilePath))
            {
                System.IO.FileInfo infor = new FileInfo(FilePath);

                log.LogFileUpLoadTime = infor.LastWriteTime;
            }
            try
            {
                string strConn;
                HW_Result2013 result = new HW_Result2013();
                //if(Path.GetExtension(filePath)==".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                //else
                //    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";  
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                System.Data.DataTable table = OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string tableName = string.Empty;
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    if (table.Rows[i]["Table_Name"].ToString().EndsWith("$"))
                    {
                        tableName = table.Rows[i]["Table_Name"].ToString();
                        break;
                    }
                }
                String sql = "SELECT top(1) * FROM [" + tableName + "] where F2 is not null and (F3='男' or F3='女)'";//可是更改Sheet名称，比如sheet2，等等  
                //OleDbCommand comm=new OleDbCommand (sql,OleConn);
                //OleDbDataReader reader = comm.ExecuteReader();

                //StringBuilder serversql=new System.Text.StringBuilder ();
                //serversql.Append("insert into HW_Result2028 values(");
                //if (reader.Read())
                //{
                //    //serversql.Append(DateTime.Now.Year.ToString()+"1"+",") ;
                //    serversql.Append("");//学生编码
                //    serversql.Append(SchoolCode+",");//cityid
                //    serversql.Append(",");//年级编号
                //    serversql.Append(",");//班级编号
                //    serversql.Append(",");//入学时间
                //    serversql.Append(",");//学号
                //    serversql.Append(SchoolName+",");//学校名
                //    serversql.Append(",");//年级
                //    serversql.Append(",");//班级
                //    serversql.Append(reader[1]+",");
                //    serversql.Append(reader[2] + ",");
                //    serversql.Append(reader[3] + ",");
                //    serversql.Append(reader[4] + ",");
                //    serversql.Append(reader[5] + ",");
                //    serversql.Append(reader[6] + ",");
                //    serversql.Append(DateTime.Now + ",");//体检日期
                //    serversql.Append(reader[1] + ",");//年龄
                //    serversql.Append(reader[1] + ",");//mark

                //    serversql.Append(reader[11] + ",");//身高
                //    serversql.Append(reader[1] + ",");//身高等级
                //    serversql.Append(reader[1] + ",");//身高等级D
                //    serversql.Append(reader[1] + ",");//身高

                //    serversql.Append(reader[12] + ",");//体重
                //    serversql.Append(reader[1] + ",");//身高等级
                //    serversql.Append(reader[1] + ",");//身高等级D
                //    serversql.Append(reader[1] + ",");//身高

                //    serversql.Append(reader[1] + ",");//营养评价
                //    serversql.Append(reader[1] + ",");//营养评价X
                //    serversql.Append(reader[1] + ",");//tx评价
                //    serversql.Append(reader[1] + ",");//fy评价X

                //    serversql.Append(reader[13] + ",");//胸围
                //    serversql.Append(reader[1] + ",");//身高等级
                //    serversql.Append(reader[1] + ",");//身高等级D
                //    serversql.Append(reader[1] + ",");//身高

                //    serversql.Append(reader[14] + ",");//肺活量
                //    serversql.Append(reader[1] + ",");//身高等级
                //    serversql.Append(reader[1] + ",");//身高等级D
                //    serversql.Append(reader[1] + ",");//身高

                //    serversql.Append(reader[1] + ",");//握力
                //    serversql.Append(reader[1] + ",");//身高等级
                //    serversql.Append(reader[1] + ",");//身高等级D
                //    serversql.Append(reader[1] + ",");//身高

                //    serversql.Append(reader[17] + ",");//脉搏
                //    serversql.Append(reader[1] + ",");//身高等级


                //    serversql.Append(reader[18] + ",");//收缩压
                //    serversql.Append(reader[1] + ",");//身高等级

                //    serversql.Append(reader[19] + ",");//收张压
                //    serversql.Append(reader[1] + ",");//身高等级

                //    serversql.Append(reader[16] + ",");//左边视力
                //    serversql.Append(reader[15] + ",");//右边视力



                //}
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet1");
                OleConn.Close();

                log.LogResult = true;
                return OleDsExcle;
            }
            catch (Exception err)
            {
                log.LogResult = false;
                log.LogError = err.Message;
                throw err;
                //记录日记
            }
            finally
            {
                logManager.WriteLog(log);
            }
        }
        public string TestData(DataSet ds,string schoolCode,string schoolName)
        {
            DataTable dt = ds.Tables[0];
            UploadResultLog log = new UploadResultLog();
            log.LogType = "测试数据";
            log.LogTime = DateTime.Now;
            log.LogFileUpLoadTime = DateTime.Now;
           
            log.LogSchoolCode = schoolCode;
            log.LogSchoolName = schoolName;

            string error = string.Empty;
            HW_Result2013 Result = new HW_Result2013();
            try
            {
                if (dt.Rows.Count > 0)
                {
                    HW_Result2013Manager resultManager = new HW_Result2013Manager();
            //        Result.TJND6=dt.Rows[0][];
            //Result.BH=dt.Rows[0][];
            //Result.CHECKID=dt.Rows[0][];
            //Result.SCHOOLBH=dt.Rows[0][];
            //Result.GRADEBH4=dt.Rows[0][];
            //Result.CLASSBH2=dt.Rows[0][];
            //Result.RXSJ=dt.Rows[0][];
            //Result.XH=dt.Rows[0][];
            //Result.SCHOOL_NAME=dt.Rows[0][];
            //Result.GRADE_NAME=dt.Rows[0][];
            //Result.CLASS_NAME=dt.Rows[0][];
            //Result.XM=dt.Rows[0][];
            //Result.XB=dt.Rows[0][];
            //Result.CSRQ=dt.Rows[0][];
            //Result.MZ=dt.Rows[0][];
            //Result.ZZMM=dt.Rows[0][];
            //Result.SY=dt.Rows[0][];
            //Result.TJRQ=dt.Rows[0][];
            //Result.NL=dt.Rows[0][];
            //Result.MARK=dt.Rows[0][];
            //Result. SG=dt.Rows[0][];
            //Result.SGDJQ=dt.Rows[0][];
            //Result.SGDJD=dt.Rows[0][];
            //Result.SGBJ=dt.Rows[0][];
            //Result. TZ=dt.Rows[0][];
            //Result.TZDJQ=dt.Rows[0][];
            //Result.TZDJD=dt.Rows[0][];
            //Result.TZBJ=dt.Rows[0][];
            //Result.YYPJ=dt.Rows[0][];
            //Result.YYPJX=dt.Rows[0][];
            //Result.TXPJ=dt.Rows[0][];
            //Result.FYPJ=dt.Rows[0][];
            //Result. XW=dt.Rows[0][];
            //Result.XWDJQ=dt.Rows[0][];
            //Result.XWDJD=dt.Rows[0][];
            //Result.XWBJ=dt.Rows[0][];
            //Result. FHL=dt.Rows[0][];
            //Result.FHLDJQ=dt.Rows[0][];
            //Result.FHLDJD=dt.Rows[0][];
            //Result.FHLBJ=dt.Rows[0][];
            //Result. WL=dt.Rows[0][];
            //Result.WLDJQ=dt.Rows[0][];
            //Result.WLDJD=dt.Rows[0][];
            //Result.WLBJ=dt.Rows[0][];
            //Result. MB=dt.Rows[0][];
            //Result.MBBJ=dt.Rows[0][];
            //Result. SSY=dt.Rows[0][];
            //Result.SSYBJ=dt.Rows[0][];
            //Result. SZY=dt.Rows[0][];
            //Result.SZYBJ=dt.Rows[0][];
            //Result. LSL=dt.Rows[0][];
            //Result. RSL=dt.Rows[0][];
            //Result.QTYB=dt.Rows[0][];
            //Result.LQG=dt.Rows[0][];
            //Result.RQG=dt.Rows[0][];
            //Result.LSY=dt.Rows[0][];
            //Result.RSY=dt.Rows[0][];
            //Result.BS=dt.Rows[0][];
            //Result.TL=dt.Rows[0][];
            //Result.XJ=dt.Rows[0][];
            //Result.BB=dt.Rows[0][];
            //Result.QSBNUM=dt.Rows[0][];
            //Result.RQB=dt.Rows[0][];
            //Result.RQS=dt.Rows[0][];
            //Result.RQH=dt.Rows[0][];
            //Result.HQB=dt.Rows[0][];
            //Result.HQS=dt.Rows[0][];
            //Result.HQH=dt.Rows[0][];
            //Result.YZB=dt.Rows[0][];
            //Result.BTT=dt.Rows[0][];
            //Result.XK=dt.Rows[0][];
            //Result.JZ=dt.Rows[0][];
            //Result.SZ=dt.Rows[0][];
            //Result.PZ=dt.Rows[0][];
            //Result.XZ=dt.Rows[0][];
            //Result.GP=dt.Rows[0][];
            //Result.FEI=dt.Rows[0][];
            //Result.XT=dt.Rows[0][];
            //Result.XHDB=dt.Rows[0][];
            //Result.FHCL=dt.Rows[0][];
            //Result.PFB=dt.Rows[0][];
            //Result. ZG=dt.Rows[0][];
            //Result.ZGDJQ=dt.Rows[0][];
            //Result.ZGDJD=dt.Rows[0][];
            //Result.ZGBJ=dt.Rows[0][];
            //Result. JK=dt.Rows[0][];
            //Result.JKDJQ=dt.Rows[0][];
            //Result.JKDJD=dt.Rows[0][];
            //Result.JKBJ=dt.Rows[0][];
            //Result. GPK=dt.Rows[0][];
            //Result.GPKDJQ=dt.Rows[0][];
            //Result.GPKDJD=dt.Rows[0][];
            //Result.GPKBJ=dt.Rows[0][];
            //Result. BJL=dt.Rows[0][];
            //Result. GSTJ=dt.Rows[0][];
            //Result. JJX=dt.Rows[0][];
            //Result.BMKY=dt.Rows[0][];
            //Result.SJSR=dt.Rows[0][];
            //Result. WSM=dt.Rows[0][];
            //Result.WSMDJQ=dt.Rows[0][];
            //Result.WSMDJD=dt.Rows[0][];
            //Result.WSMBJ=dt.Rows[0][];
            //Result. LDTY=dt.Rows[0][];
            //Result.LDTYDJQ=dt.Rows[0][];
            //Result.LDTYDJD=dt.Rows[0][];
            //Result.LDTYBJ=dt.Rows[0][];
            //Result. LL=dt.Rows[0][];
            //Result.LLDJQ=dt.Rows[0][];
            //Result.LLDJD=dt.Rows[0][];
            //Result.LLBJ=dt.Rows[0][];
            //Result. NNL=dt.Rows[0][];
            //Result.NNLDJQ=dt.Rows[0][];
            //Result.NNLDJD=dt.Rows[0][];
            //Result.NNLBJ=dt.Rows[0][];
            //Result. ZWT=dt.Rows[0][];
            //Result.ZWTDJQ=dt.Rows[0][];
            //Result.ZWTDJD=dt.Rows[0][];
            //Result.ZWTBJ=dt.Rows[0][];
            //Result.T1=dt.Rows[0][];
            //Result.T2=dt.Rows[0][];
            //Result.T3=dt.Rows[0][];
            //Result.T4=dt.Rows[0][];
            //Result.T5=dt.Rows[0][];
            //Result.T6=dt.Rows[0][];
            //Result.T7=dt.Rows[0][];
            //Result.T8=dt.Rows[0][];
            //Result.T9=dt.Rows[0][];
            //Result.T10=dt.Rows[0][];
            //Result.SLBH=dt.Rows[0][];
            //Result.MBPJ=dt.Rows[0][];
            //Result.SSYPJ=dt.Rows[0][];
            //Result.SZYPJ=dt.Rows[0][];
            //Result.PXPJ=dt.Rows[0][];
            //Result.SLPJ=dt.Rows[0][];
            //Result.XJH=dt.Rows[0][];
            //Result.YYPJB=dt.Rows[0][];
            //Result.TJSY=dt.Rows[0][];
            //Result.MB1=dt.Rows[0][];
            //Result.MB2=dt.Rows[0][];
            //Result.MB3=dt.Rows[0][];
            //Result.TJFS=dt.Rows[0][];
            //Result.TJPJ=dt.Rows[0][];
            //Result.YYPJH=dt.Rows[0][];
            //Result.XMPY=dt.Rows[0][];
            //Result. HXP=dt.Rows[0][];
            //Result.HXPPJ=dt.Rows[0][];
            //Result.EB=dt.Rows[0][];
            //Result.YB=dt.Rows[0][];
            //Result.TB=dt.Rows[0][];
            //Result.JB=dt.Rows[0][];
            //Result.PF=dt.Rows[0][];
            //Result.JZX=dt.Rows[0][];
            //Result.LBJ=dt.Rows[0][];
            //Result.JHJS=dt.Rows[0][];
            //Result. GBZAM=dt.Rows[0][];
            //Result. DHSA=dt.Rows[0][];
            //Result. DHSZ=dt.Rows[0][];
            //Result.GG=dt.Rows[0][];
            //Result.BMKT=dt.Rows[0][];
            //Result.YGHKT=dt.Rows[0][];
            //Result.YGEKY=dt.Rows[0][];
            //Result.YGEKT=dt.Rows[0][];
            //Result.BLOOD=dt.Rows[0][];
            //Result. YW=dt.Rows[0][];
            //Result. TW=dt.Rows[0][];
            //Result.CXBH=dt.Rows[0][];
            //Result. LJSL=dt.Rows[0][];
            //Result. RJSL=dt.Rows[0][];
            //Result.JMY=dt.Rows[0][];
            //Result.WGFB=dt.Rows[0][];
            //Result.YYB=dt.Rows[0][];
            //Result.PEI=dt.Rows[0][];
            //Result. LSLS=dt.Rows[0][];
            //Result. RSLS=dt.Rows[0][];
            //Result. LJSLS=dt.Rows[0][];
            //Result. RJSLS=dt.Rows[0][];
            //Result.RTL=dt.Rows[0][];
            //Result.WSZQ=dt.Rows[0][];
            //Result.RS=dt.Rows[0][];
            //Result. BXP=dt.Rows[0][];
            //Result. XXB=dt.Rows[0][];
            //Result.SFZH=dt.Rows[0][];
            //Result.DH=dt.Rows[0][];
            //Result.ADDRESS2=dt.Rows[0][];
            //Result.RXYFJZS=dt.Rows[0][];
            //Result.YMA=dt.Rows[0][];
            //Result.YMAJY=dt.Rows[0][];
            //Result.YMB=dt.Rows[0][];
            //Result.YMBJY=dt.Rows[0][];
            //Result.YMC=dt.Rows[0][];
            //Result.YMCJY=dt.Rows[0][];
            //Result.YMD=dt.Rows[0][];
            //Result.YMDJY=dt.Rows[0][];
            //Result.YME=dt.Rows[0][];
            //Result.YMEJY=dt.Rows[0][];
            //Result.YMQT=dt.Rows[0][];
            //Result.RXHJZS=dt.Rows[0][];
            //Result.JWBS=dt.Rows[0][];
            //Result.QCQFY=dt.Rows[0][];
            //Result.STUDGUID=dt.Rows[0][];
            //Result.SCHOOLID=dt.Rows[0][];
            //Result.STUDNUM=dt.Rows[0][];
            //Result.YMF=dt.Rows[0][];
            //Result.YMFJY=dt.Rows[0][];
                    //结果定义
                    int n = resultManager.Add(Result);
                    if (n < 1)
                    {
                        error = "请检查数据，数据数量格式是否正确";
                        log.LogResult = false;
                        logManager.WriteLog(log);
                        return error;
                        //throw new Exception(error);
                    }
                    else
                    {
                        log.LogResult = true;
                        logManager.WriteLog(log);
                        return string.Empty;
                    }
                }
                else
                {
                    error = "请检查数据，数据数量为零";
                    log.LogResult = false;
                    logManager.WriteLog(log);
                    return error;
                }

            }
            catch(Exception e)
            {
                error = e.Message;
                log.LogError = error;
                log.LogResult = false;
                logManager.WriteLog(log);
                throw e;
            }
        }
    }
}
